import java.io.BufferedReader;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.Map;

import org.sqlite.SQLiteDataSource;

public class RiskServer extends JSONPServer {
	public static void main(String[] args) throws Exception {
		new RiskServer().start();
	}

	Connection conn;
	PreparedStatement selectRisk;

	public RiskServer() {
		super();
	}

	public void dispatch(String url, BufferedReader r, PrintWriter w)
			throws Exception {
		if (url.startsWith("/getRisk")) {
			getRisk(url, r, w);
		} else {
			w.println("hello!");
		}
	}

	private void getRisk(String url, BufferedReader r, PrintWriter w)
			throws SQLException {
		if (conn == null) {
			SQLiteDataSource ds = new SQLiteDataSource();

			ds.setUrl("jdbc:sqlite:risk.db");
			conn = ds.getConnection();
			selectRisk = conn
					.prepareStatement(""
							+ "select p.symbol, n.name, r.avg, r.beta, r.std, r.sr, r.te, r.ir "
							+ "from price p inner join risk r on p.symbol = r.symbol "
							+ "and p.pricedate = r.pricedate inner join stockname n on p.symbol = n.symbol "
							+ "where p.symbol in (select symbol from indexstocks where indexname = ?) "
							+ "and p.pricedate = (select max(pricedate) from price pm) "
							+ "order by r.beta desc");
		}

		Map<String, String> param = createParameterMap(url);

		selectRisk.setString(1, param.get("indexname"));
		ResultSet rs = selectRisk.executeQuery();
		NumberFormat pct = NumberFormat.getPercentInstance();
		DecimalFormat frac = new DecimalFormat("0.0#");

		if (rs.next()) {
			w.print("<tr>");
			w.print("<td>");
			w.print(rs.getString("symbol"));
			w.print("</td>");
			w.print("<td>");
			w.print(rs.getString("name"));
			w.print("</td>");
			w.print("<td>");
			w.print(pct.format(rs.getDouble("avg")));
			w.print("</td>");
			w.print("<td>");
			w.print(frac.format(rs.getDouble("beta")));
			w.print("</td>");
			w.print("<td>");
			w.print(pct.format(rs.getDouble("std")));
			w.print("</td>");
			w.print("<td>");
			w.print(pct.format(rs.getDouble("sr")));
			w.print("</td>");
			w.print("<td>");
			w.print(pct.format(rs.getDouble("ir")));
			w.print("</td>");
			w.println("</tr>");
		}

		rs.close();
	}
}
